declare @str varchar(2000)
	, @str2 nvarchar(2000)
	, @str3 nvarchar(2000)
	, @server varchar(20)

if object_id('tempdb.dbo.#tx','U') is not null
drop table #tx
create table #tx (srvname varchar(20), year smallint, month tinyint, AvgDailyMinTxPerSec numeric(12,2), AvgDailyMaxTxPerSec numeric(12,2))

if object_id('tempdb.dbo.#io','U') is not null
drop table #io
create table #io (srvname varchar(20), year smallint, month tinyint, AvgDailyReads bigint, AvgDailyWrites bigint)

set @str = 'select cast(''''##server##'''' as varchar(20)),
  Datepart(yyyy, p1.SampleDate) as year, 
	Datepart(mm, p1.SampleDate) as month,
	cast(avg(p1.MinTxnPerSec) as numeric(12,2)) as [AvgDailyMinTxn/Sec], 
	cast(avg(p1.MaxTxnPerSec) as numeric(12,2)) as [AvgDailyMaxTxn/Sec]
from admin.dbo.dbperfhistory p1
inner join admin.dbo.dbperfhistory p2
on p1.SampleDate = p2.SampleDate
where p1.dbName = ''''_Total''''
and Datepart(d, p1.SampleDate) = 1
and p2.DbName = ''''_Total''''
group by p1.dbName,Datepart(yyyy, p1.SampleDate),Datepart(mm, p1.SampleDate)
order by case p1.DBName when ''''_Total'''' then 1 else 0 end, p1.dbname, Datepart(yyyy, p1.SampleDate) desc,Datepart(mm, p1.SampleDate) desc'

set @str2 = replace(@str,'##server##','APOLLO')
set @str3 = 'select * from OPENQUERY(lnkAPOLLO,''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','ATHENA')
set @str3 = 'select * from OPENQUERY(lnkATHENA,''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-QUOTESP101')
set @str3 = 'select * from OPENQUERY([lnkSQ-QUOTESP101],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','WORLDSCOPE2')
set @str3 = 'select * from OPENQUERY(lnkWORLDSCOPE,''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','ZEUS')
set @str3 = 'select * from OPENQUERY(lnkZEUS,''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-DMP101')
set @str3 = 'select * from OPENQUERY([lnkSQ-DMP101],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-DMP102')
set @str3 = 'select * from OPENQUERY([lnkSQ-DMP102],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-DEWEYP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-DEWEYP201],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-NEWYORKP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-NEWYORKP201],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-LONDONP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-LONDONP201],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-PARISP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-PARISP201],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-TOKYOP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-TOKYOP201],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str = 'select cast(''''##server##'''' as varchar(20)),
  Datepart(yyyy, s1.lastrun), 
	Datepart(mm, s1.lastrun),
	sum(cast(s1.total_read as bigint) - cast(s2.total_read as bigint)),
	sum(cast(s1.total_write as bigint) - cast(s2.total_write as bigint))
from admin.dbo.spt_monitor_log s1
left join admin.dbo.spt_monitor_log s2
on s1.Id = s2.Id + 1
where s1.cpu_busy > s2.cpu_busy
and s1.io_busy > s2.io_busy
and s1.idle > s2.idle
group by 	Datepart(mm, s1.lastrun), Datepart(yyyy, s1.lastrun)
order by Datepart(yyyy, s1.lastrun) desc,Datepart(mm, s1.lastrun) desc'

set @str2 = replace(@str,'##server##','APOLLO')
set @str3 = 'select * from OPENQUERY(lnkAPOLLO,''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','ATHENA')
set @str3 = 'select * from OPENQUERY(lnkATHENA,''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-QUOTESP101')
set @str3 = 'select * from OPENQUERY([lnkSQ-QUOTESP101],''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','WORLDSCOPE2')
set @str3 = 'select * from OPENQUERY(lnkWORLDSCOPE,''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','ZEUS')
set @str3 = 'select * from OPENQUERY(lnkZEUS,''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-DMP101')
set @str3 = 'select * from OPENQUERY([lnkSQ-DMP101],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-DMP102')
set @str3 = 'select * from OPENQUERY([lnkSQ-DMP102],''' + @str2 + ''')'
insert #tx
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-DEWEYP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-DEWEYP201],''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-NEWYORKP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-NEWYORKP201],''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-LONDONP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-LONDONP201],''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-PARISP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-PARISP201],''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

set @str2 = replace(@str,'##server##','SQ-TOKYOP201')
set @str3 = 'select * from OPENQUERY([lnkSQ-TOKYOP201],''' + @str2 + ''')'
insert #io
exec sp_executesql @str3

select case when t.srvname is null then i.srvname else t.srvname end [Server]
	, case when t.year is null then i.year else t.year end [Year]
	, case when t.month is null then i.month else t.month end [Month]
	, t.AvgDailyMinTxPerSec, AvgDailyMaxTxPerSec, i.AvgDailyReads, i.AvgDailyWrites 
from #tx t
full outer join #io i
on t.srvname = i.srvname
and t.year = i.year
and t.month = i.month
order by [Server], [Year] desc, [Month] desc